<?php
namespace app\examScore\controller;
use app\common\Base;
use app\common\Excel;
use think\Db;
//--学生各科实力
class ClazzSubjectStrength extends Base {
	
	public function index() {
		return $this->fetch();
	}

	public function pageQuery() {
		$examId = input("examId");
		$subject = input("subject");
		if(!$examId) return [];
		$sql = "select 实验班,班级,学科,任课教师,convert(decimal(18,1),总分) 总分,人数,convert(decimal(18,2),人平分) 人平分,特尖人数,convert(decimal(8,3),特尖率) 特尖率,特优人数,convert(decimal(8,3),特优率) 特优率,优秀人数,convert(decimal(8,3),优秀率) 优秀率,合格人数,convert(decimal(8,3),合格率) 合格率,低分人数,convert(decimal(8,3),低分率) 低分率,round(最高分,1) 最高分,round(最低分,1) 最低分,convert(decimal(8,2),实力) 实力,实力排名
				from 成绩汇总
				where 考试ID=".$examId." and 学科='".$subject."'
				UNION 
				select 9,班级,学科,'年级汇总',sum(总分),sum(人数),convert(decimal(18,2),sum(总分)/sum(人数)),
				sum(特尖人数),convert(decimal(18,3),sum(特尖人数)*1.0/sum(人数)),
				sum(特优人数),convert(decimal(18,3),sum(特优人数)*1.0/sum(人数)),sum(优秀人数),convert(decimal(18,3),sum(优秀人数)*1.0/sum(人数)),
				sum(合格人数),convert(decimal(18,3),sum(合格人数)*1.0/sum(人数)),sum(低分人数),convert(decimal(18,3),sum(低分人数)*1.0/sum(人数)),
				max(最高分),min(最低分),NULL,NULL 
				from 成绩汇总 
				where 考试ID=".$examId." and 学科='".$subject."' and 任课教师='年级汇总' 
				group by 班级,学科
				order by 实验班,班级";
		$Emodel = new Emodel();
		$result = selectResult($Emodel->query($sql));
		return $this::getResult($result);
	}

	public function examSubject(){
		$where["考试ID"] = input("examId");
		$result = Db::table("成绩汇总")
			   ->field("学科 subject")
			   ->distinct(true)
			   ->where($where)
			   ->select();
		$data = [];
		foreach ($result as $key => $obj) {
			$data[$key] = array("text"=>$obj["subject"],"value"=>$obj["subject"]);
		}
		return $data;
	}

	public function exportExcel() {
		$examId = input("examId");
		$subject = input("subject");
		if(!$examId) return [];
		$sql = "select 实验班,班级,学科,任课教师,round(总分,1) 总分,人数,convert(decimal(18,2),人平分) 人平分,特尖人数,convert(decimal(8,3),特尖率) 特尖率,特优人数,convert(decimal(8,3),特优率) 特优率,优秀人数,convert(decimal(8,3),优秀率) 优秀率,合格人数,convert(decimal(8,3),合格率) 合格率,低分人数,convert(decimal(8,3),低分率) 低分率,round(最高分,1) 最高分,round(最低分,1) 最低分,convert(decimal(8,2),实力) 实力,实力排名
				from 成绩汇总
				where 考试ID=".$examId." and 学科='".$subject."'
				UNION 
				select 9,班级,学科,'年级汇总',sum(总分),sum(人数),convert(decimal(18,2),sum(总分)/sum(人数)),
				sum(特尖人数),convert(decimal(18,3),sum(特尖人数)*1.0/sum(人数)),
				sum(特优人数),convert(decimal(18,3),sum(特优人数)*1.0/sum(人数)),sum(优秀人数),convert(decimal(18,3),sum(优秀人数)*1.0/sum(人数)),
				sum(合格人数),convert(decimal(18,3),sum(合格人数)*1.0/sum(人数)),sum(低分人数),convert(decimal(18,3),sum(低分人数)*1.0/sum(人数)),
				max(最高分),min(最低分),NULL,NULL 
				from 成绩汇总 
				where 考试ID=".$examId." and 学科='".$subject."' and 任课教师='年级汇总' 
				group by 班级,学科
				order by 实验班,班级";
		$result = Db::query($sql);
		$result = selectResult($result);
		$result = $this::getResult($result);
		$fields = "班级类别,班级,学科,任课教师,总分,人数,人平分,特尖人数,特优人数,特优率,优秀人数,优秀率,合格人数,合格率,低分人数,低分率,最高分,最低分,实力,实力排名";
		//excel表头内容
		$list = explode(",", $fields);
		$header = [];
		foreach ($list as $key=> $value){
			$header[$value] = $value;
		}
		Excel::toExcel($result,'班级各科实力-'.$subject,$header);
		exit;
	}

	static protected function getResult($result) {
		foreach($result as $key => $obj){
			$temp = $obj;
			foreach($obj as $key2 => $value){
				if($key2 == '实验班') {
					if($value == 0) $temp["班级类别"] = "天佑班";
					else if($value == 1) $temp["班级类别"] = "有为班";
					else if($value == 2) $temp["班级类别"] = "火箭班";
					else if($value == 3) $temp["班级类别"] = "国际班";
					else $temp["班级类别"] = "全年级";
					break;
				}
	    	}
	    	$result[$key] = $temp;
	    }
		return $result;
	}
}
